package generator

import generator.common.ExcelWriter
import generator.entity.{Row, RowInfo}
import generator.util.GenerateFromXSSFExcel
import org.apache.poi.xssf.usermodel.XSSFSheet

import scala.collection.mutable.ListBuffer
import scala.util.matching.Regex

object GeneratorExcel {
  val DevFileName: String = "datas/梳理对应关系.xlsx"
  val writeFile = "datas/对应关系.xlsx"

  def generateExcel(list:List[RowInfo]): Unit = {
    var sheetIndex = 0
    list.groupBy(_.fromTableName).foreach {
      case (fromTableName, list) => {
        sheetIndex += 1
        if (!test) init(writer, sheetIndex, fromTableName)
        var rowId = 1
        println(s"$fromTableName now")

        val uniqueList:List[RowInfo] =  list.distinct.sortBy(_.fromTableField)
        for (row <- uniqueList) {
//          val TransformInfo = getTransform(row)
//          val rowInfo = TransformInfo._2
          if (!test) {
            writer.writerToXSSFSheet(fromTableName, writeFile, row.fromTableName, sheetIndex, rowId, 0)
            writer.writerToXSSFSheet(fromTableName, writeFile, row.fromTableField, sheetIndex, rowId, 1)
            writer.writerToXSSFSheet(fromTableName, writeFile, row.msg, sheetIndex, rowId, 2)
            writer.writerToXSSFSheet(fromTableName, writeFile, row.cj2_tableName, sheetIndex, rowId, 4)


            writer.writerToXSSFSheet(fromTableName, writeFile, row.transformExp, sheetIndex, rowId, 5)


            writer.writerToXSSFSheet(fromTableName, writeFile, row.fromExp.trim, sheetIndex, rowId, 6)
            writer.writerToXSSFSheet(fromTableName, writeFile, row.cj2_field, sheetIndex, rowId, 7)
            writer.writerToXSSFSheet(fromTableName, writeFile, row.cj1_tableName, sheetIndex, rowId, 8)

            rowId += 1
          }

        }
        println(s"$fromTableName complete")
      }
    }
    if (!test) writer.write(writeFile)
  }

  def main(args: Array[String]): Unit = {
    val generatorDev = GenerateFromXSSFExcel[List[RowInfo]](DevFileName, 2, getDataSourceDev)
    generatorDev.generate(generateExcel)
  }

  def getSource(allSource: String): String = {
    val Source = allSource.replace(" FROM ", " @ ").replace("--", "#")
    var flag = 0
    var comment = false
    var mutlComment = false
    var str = ""
    for (c <- Source) {
      if (c == '#') comment = true
      if (c == '\n' && comment == true) comment = false

      if (c == '(' && !comment) flag += 1
      else if (c == ')' && !comment) flag -= 1
      else if (c == '@' && flag == 0) {
        return str
      }
      if (c != '@' && c != '#') str += c
      else if (c == '@') str += " FROM "
      else if (c == '#') str += "--"
    }
    str
  }

  def getTables(allSource: String): String = {
    val Source = allSource.replace(" FROM ", " @ ").replace("--", "#")
    var flag = 0
    var comment = false
    var str = ""
    for (c <- Source) {
      if (c == '#') comment = true
      if (c == '\n' && comment == true) comment = false
      if (c == '(' && !comment) flag += 1
      else if (c == ')' && !comment) flag -= 1
      else if (c == '@' && flag == 0) {
        str = ""
      }
      if (c != '@') str += c
      else if (c == '@' && flag != 0) str += " FROM "
    }
    str
  }

  def init(writer: ExcelWriter, sheetIndex: Int, sheetName: String): Unit = {
    writer.createSheet(sheetIndex, sheetName, writeFile)

    writer.writerToXSSFSheet(sheetName, writeFile, "表名", sheetIndex, 0, 0)
    writer.writerToXSSFSheet(sheetName, writeFile, "字段", sheetIndex, 0, 1)
    writer.writerToXSSFSheet(sheetName, writeFile, "说明", sheetIndex, 0, 2)
    writer.writerToXSSFSheet(sheetName, writeFile, "是否同步", sheetIndex, 0, 3)
    writer.writerToXSSFSheet(sheetName, writeFile, "表名", sheetIndex, 0, 4)

    writer.writerToXSSFSheet(sheetName, writeFile, "字段来源", sheetIndex, 0, 5)

    writer.writerToXSSFSheet(sheetName, writeFile, "其他说明", sheetIndex, 0, 6)
    writer.writerToXSSFSheet(sheetName, writeFile, "验证sql", sheetIndex, 0, 7)
  }

  def getFromTable(cj1_tableName: String, str: String, tablesList: ListBuffer[(String, String)]): (String, String) = {
    var back = ("NULL", "NULL")
    if (tablesList.length == 1) back = (tablesList(0)._1, "NULL")
    if (str.contains("SELECT")) {
      return back
    }

    for (i <- 0 until (tablesList.length)) {
      if (tablesList(i)._2 == "NULL") {
        back = (tablesList(i)._1.trim, "NULL")
      } else {
        val sourcePatternPlus = new Regex(s"${tablesList(i)._2}\\.([A-Z0-9]+(_[A-Z0-9]+)*)")
        val maybeMatch = sourcePatternPlus.findFirstMatchIn(str)
        if (!maybeMatch.isEmpty) {
          return (tablesList(i)._1.trim, maybeMatch.get.group(1).trim)
        }
      }
    }
    back
  }

  def replaceFromTable(str: String, tablesList: ListBuffer[(String, String)]): String = {
    if (str.contains("SELECT")) {
      return str
    }
    var result = str
    for (i <- 0 until (tablesList.length)) {
      val sourcePatternPlus = new Regex(s"${tablesList(i)._2}\\.([A-Z0-9]+(_[A-Z0-9]+)*)")
      if (!sourcePatternPlus.findFirstMatchIn(str).isEmpty) {
        return sourcePatternPlus.replaceAllIn(str, m => {
          s"${tablesList(i)._1}.${m.group(1)}"
        })
      }
    }
    str
  }

  def canTransform(tarStr: String): Boolean = {
    !isSelect(tarStr)
  }

  def isSelect(tarStr: String): Boolean = {
    tarStr.contains("SELECT") && tarStr.contains("FROM")
  }

  def isCaseEnd(tarStr: String): Boolean = {
    tarStr.contains("CASE") && tarStr.contains("END")
  }

  def getReCondition(condition: String, row: RowInfo): String = {
    val pattern = new Regex(s"^${row.fromTableName}\\.${row.fromTableField}\\s*=([^\n]+)")
    val maybeMatch = pattern.findFirstMatchIn(condition.trim)
    if (!maybeMatch.isEmpty) {
      return maybeMatch.get.group(1)
    } else {
      "    need transform=>" + condition
    }
  }

  def isVal(str: String, row: RowInfo, funcList: List[String]): Boolean = {
    var tarStr = str.trim
    tarStr = tarStr.replace(" AS " + row.cj2_field, "")
    tarStr = tarStr.replace(" " + row.cj2_field, "")
    val pattern = new Regex("(^'[^\n]*'$|^NULL$|^[0-9]+$)")
    if (!pattern.findFirstMatchIn(tarStr).isEmpty) {
      return true
    }
    for (func <- funcList) {
      val patternFunc = new Regex(s"^$func\\((([A-Z_]+),?\\s*|'[^']*',?\\s*|[0-9]+,?\\s*)+\\)" + '$')
      val maybeMatch = patternFunc.findFirstMatchIn(tarStr)
      if (!maybeMatch.isEmpty) {
        if (maybeMatch.get.group(2) == null) {
          return true
        }
      }
    }
    false
  }

  def getTransform(row: RowInfo): (String, RowInfo, String) = {
    val empty = "\\s*\n*\\s*"
    var msg = ""
    var rowInfo = row
    val funcList = List[String]("F_TRANSORG", "F_GET_CODE", "NVL", "DECODE", "F_TRANSCODE", "TO_CHAR", "TO_DATE")
    var tarStr = row.fromExp.trim

    val prefix = s"(CASE$empty)"
    val whenthen = s"(@([^#]*)#$empty([^\n@~]*)$empty)"
    val whenthens = s"($whenthen+)"
    val elseStr = s"(${empty}~${empty}([^\n@~]*))?"
    val End = '(' + s"${empty}END" + ')'
    val pattern = new Regex(s"\\(?${prefix + whenthens + elseStr + End}\\)?")
    val pattern1 = new Regex(prefix)
    val pattern2 = new Regex(prefix + whenthens)
    val pattern3 = new Regex(prefix + whenthens + elseStr)


    if (isSelect(tarStr) && isCaseEnd(tarStr) && !tarStr.contains("SELECT CASE")) {
//      tarStr = tarStr.replace(" AS " + row.cj2_field, "")
//      tarStr = tarStr.replace(" " + row.cj2_field, "")
//      tarStr = tarStr.replaceAll("WHEN\n?\\s?", "@").replaceAll("THEN\n?\\s?", "#").replaceAll("ELSE\n?\\s?", "~")
//      val maybeMatch = pattern.findFirstMatchIn(tarStr)
//      if (!maybeMatch.isEmpty) {
//        maybeMatch.get.subgroups
//      }
      return ("", rowInfo, "无法转换")
    }
    if (isSelect(tarStr)) {
      tarStr = tarStr.replace(" AS " + row.cj2_field, "")
      tarStr = tarStr.replace(" " + row.cj2_field, "")

      if (tarStr.head=='('&&tarStr.last==')') {
        tarStr = tarStr.substring(1,tarStr.length-1)
      }

      val parts = getPartsFromSelectSql(tarStr)
      var selectPart = parts._1
      val fromPart = getStrBeforeWhere(parts._2.replace("WHERE", "%"))
      val selectPattern = new Regex(s"SELECT$empty")
      selectPart = selectPattern.replaceFirstIn(selectPart.trim,"")

      val tablesPattern = new Regex(s"($empty[^\\s]+)(\\s+($name))?$empty")
      val tablesList = getTableList(fromPart.trim, tablesPattern)


      val fromTableInfo = getFromTable(row.cj1_tableName, selectPart, tablesList)
      rowInfo = RowInfo(row.cj1_tableName, fromTableInfo._1, fromTableInfo._2, replaceFromTable(selectPart, tablesList), row.cj2_tableName, row.cj2_field)
      tarStr=rowInfo.fromExp
      msg = "映射"


//      return ("", rowInfo, "映射")
    }

    val realField = rowInfo.fromTableName + "." + rowInfo.fromTableField
    val cj2_realField = rowInfo.cj2_tableName + "." + rowInfo.cj2_field

    if (isCaseEnd(tarStr)) {

      tarStr = tarStr.replace(" AS " + row.cj2_field, "")
      tarStr = tarStr.replace(" " + row.cj2_field, "")
      tarStr = tarStr.replaceAll("WHEN\n?\\s?", "@").replaceAll("THEN\n?\\s?", "#").replaceAll("ELSE\n?\\s?", "~")


      val maybeMatch = pattern.findFirstMatchIn(tarStr)
      if (!maybeMatch.isEmpty) {
        val regex = new Regex(whenthen)
        tarStr = pattern.replaceAllIn(tarStr, matcher => {
          var elseStr = ""
          if (matcher.group(7) != null) {
            if (!isVal(matcher.group(7), rowInfo, funcList)) {
              msg = "根据1.0的字段判断后得到的结果还是1.0的字段，未知"
              elseStr = s"\nELSE\n\\?\\?\\?"
            }
            else elseStr = s"@${row.cj2_field}=${matcher.group(7)}#\\?\\?\\?"
          }
          "CASE" + regex.replaceAllIn(matcher.group(2), matcher => {
            val condition = matcher.group(2)
            val result = matcher.group(3)
            if (!isVal(result, row, funcList)) {
              msg = "根据1.0的字段判断后得到的结果还是1.0的字段，未知"
            }
            s"@${row.cj2_field}=${result}#${getReCondition(condition, row)}"
          }) + elseStr + "\nEND"
        })
      }
      tarStr = tarStr.replaceAll("@", " \nWHEN  ").replaceAll("#", " \nTHEN\n ")
    }
    else if (tarStr.contains(realField)) {
      tarStr = tarStr.replaceAll(realField, "@")

      for (func <- funcList) {
        if (tarStr.contains(func)) {
          tarStr = tarStr.replaceAll(func, "RE_" + func)
        }
      }
      tarStr = tarStr.replace(" AS " + rowInfo.cj2_field, "")
      tarStr = tarStr.replace(rowInfo.cj2_field, "")
      tarStr = tarStr.replaceAll("@", rowInfo.cj2_field) + s" ${rowInfo.cj2_field}"
    } else {
      tarStr = tarStr.replace(" AS " + rowInfo.cj2_field, "")
      tarStr = tarStr.replace(" " + rowInfo.cj2_field, "")
      val pattern = new Regex("(^'[^\n]*'$|^NULL$|^[0-9]+$)")
      if (!pattern.findFirstMatchIn(tarStr).isEmpty) {
        tarStr = ""
        msg = "常量"
        rowInfo = RowInfo(rowInfo.cj1_tableName, "NULL", rowInfo.fromTableField, rowInfo.fromExp, rowInfo.cj2_tableName, rowInfo.cj2_field)
      }
      else {

        var field = ""
        for (func <- funcList) {
          val patternFunc = new Regex(s"^$func\\((([A-Z_]+),?\\s*|'[^']*',?\\s*|[0-9]+,?\\s*)+\\)" + '$')
          val maybeMatch = patternFunc.findFirstMatchIn(tarStr)
          if (!maybeMatch.isEmpty) {
            field = maybeMatch.get.group(2)
            if (field == null) {
              tarStr = ""
              rowInfo = RowInfo(rowInfo.cj1_tableName, "NULL", rowInfo.fromTableField, rowInfo.fromExp, rowInfo.cj2_tableName, rowInfo.cj2_field)
              return (tarStr, rowInfo, "常量")
            } else {
              tarStr = tarStr.replace(func, "RE_" + func) + s" ${rowInfo.cj2_field}"
            }
          }
        }
        if (field == "") {
          val patternEq = new Regex("^([A-z]+)$")
          val maybeMatch = patternEq.findFirstMatchIn(tarStr.trim)
          if (!maybeMatch.isEmpty) {
            field = maybeMatch.get.group(1)
          }
        }
        if (field != "") {
          rowInfo = RowInfo(rowInfo.cj1_tableName, rowInfo.fromTableName, field, rowInfo.fromExp, rowInfo.cj2_tableName, rowInfo.cj2_field)
          tarStr = tarStr.replace(field, rowInfo.cj2_field)
        } else {
          msg = "未知"
          tarStr = ""
        }

      }
    }
    (tarStr.trim, rowInfo, msg)
  }

  val writer = ExcelWriter()
  var lastNum = 0
  val list = ListBuffer[Row]()

  var rowId = 1
  val test = false


  val name = "[A-Z0-9]+(_[A-Z0-9]+)*"
  val empty = "\\s*"
  val chinese = "[^\n]+"
  def getDataSourceDev(sheet: XSSFSheet): List[RowInfo] = {
    lastNum = sheet.getLastRowNum
    if (!test) writer.openXSSFWorkbook(writeFile)
    val rowInfoList = ListBuffer[RowInfo]()
    for (a <- 1 to lastNum) {
      val row = sheet.getRow(a)
      val ruleCell = row.getCell(2)
      var rule = ""
      if (ruleCell != null) rule = ruleCell.toString
      if (isValidRule(rule)) {
        val cj1_tableName = row.getCell(0).toString
        val cj2_tableName = row.getCell(1).toString
        println(s"${a + 1}:" + cj1_tableName)

        val lists = getPartsFromSql(rule)

        val fieldList = lists._1
        val sourceList = lists._2
        val tablesList = lists._3

        var Flag = false
        for (i <- 0 until fieldList.length) {
          val fromTableInfo = getFromTable(cj1_tableName, sourceList(i), tablesList)
          if (fromTableInfo._1 != "NULL" && fromTableInfo._2 == "NULL") {
            Flag = true
          }
          var rowInfo = RowInfo(cj1_tableName, fromTableInfo._1, fromTableInfo._2, replaceFromTable(sourceList(i), tablesList), cj2_tableName, fieldList(i))
          val TransformInfo = getTransform(rowInfo)
          rowInfo = RowInfo(TransformInfo._2.cj1_tableName, TransformInfo._2.fromTableName, TransformInfo._2.fromTableField, rowInfo.fromExp, TransformInfo._2.cj2_tableName, TransformInfo._2.cj2_field,TransformInfo._1,TransformInfo._3)
          rowInfoList.append(rowInfo)
        }
        if (Flag) println(s"${a + 1}:$cj1_tableName has NULL TABLE")

      }

    }
    rowInfoList.toList
  }

  def getPartsFromSelectSql(allSource: String):(String,String) = {
    (getSource(allSource),getTables(allSource))
  }

  def getPartsFromSql(sql:String):(List[String],ListBuffer[String],ListBuffer[(String,String)]) = {
    val selectPrePattern = s"SELECT${empty}(/\\*\\+${empty}PARALLEL${empty}\\($name\\)\\*/)?\n?${empty}"
    val selectPattern = s"([^\t]+)"
    val fieldPatterns = s"\\((($name,?${empty}(--[^\n]+)?(\n)?$empty)+)\\)\n${empty}"
    val insertPattern = s"INTO $name.($name)\\n${empty}"
    val prefixPattern = s"${empty}INSERT${empty}(/\\*\\+${empty}APPEND${empty}ENABLE_PARALLEL_DML${empty}PARALLEL\\($name\\)\\*/)?\n?${empty}"

    val pattern3 = new Regex(prefixPattern + insertPattern + fieldPatterns + selectPrePattern + selectPattern)




    val matcher = pattern3.findFirstMatchIn(sql).get

    val field = matcher.group(6)

    val allSource = matcher.group(13).replace("（", "(").replace("）", ")")

    val parts = getPartsFromSelectSql(allSource)
    var source = parts._1
    val tables = parts._2

    val fieldPattern = new Regex(s"(($name),?$empty(--$chinese)?\\n?$empty)")
    val fieldList = fieldPattern.findAllMatchIn(field).map(_.group(2)).toList

    val tablesPattern = new Regex(s"([^\t]+)\\s+($name)")
    val tablesList = getTableList(tables, tablesPattern)

    var sourcePatternPlus: Regex = null
    sourcePatternPlus = new Regex(s"--[^\n]*\n${empty}")
    source = sourcePatternPlus.replaceAllIn(source, m => {
      ""
    })
    source = source.replaceAll("/\\*", "@").replaceAll("\\*/", "#")
    sourcePatternPlus = new Regex(s"@[^#]*#\n?${empty}")
    source = sourcePatternPlus.replaceAllIn(source, m => {
      ""
    })
    val sourceList = getSourceList(source)
    (fieldList,sourceList,tablesList)
  }

  def getTableList(tables: String, tablesPattern: Regex) = {
    val list = ListBuffer[Tuple2[String, String]]()
    var str = ""
    var flag = 0
    for (c <- tables) {
      if (c == '(') flag += 1
      else if (c == ')') flag -= 1
      else if (c == ',' && flag == 0) {
        val matcher = tablesPattern.findFirstMatchIn(str).get
        list.append((matcher.group(1).trim, matcher.group(2)))
        str = ""
      }
      if ((c != ';' && c != '\n') && (c != ',' || flag != 0)) str += c
    }
    if (str != "") {
      if (str.contains("WHERE")) {
        str = str.replace("WHERE", "%")
        val lastTable = getStrBeforeWhere(str)

        //        val prePattern = new Regex("([^\t]+?)WHERE")
        //        val lastTable = prePattern.findFirstMatchIn(str).get.group(1)
        val matcher = tablesPattern.findFirstMatchIn(lastTable).get
        if (matcher.group(2).contains('.')) list.append((matcher.group(1).trim + ' ' + matcher.group(2), ""))
        else list.append((matcher.group(1).trim, matcher.group(2)))
      } else {
        val matcher = tablesPattern.findFirstMatchIn(str).get
        if (matcher.group(2) != null && matcher.group(2).contains('.')) list.append((matcher.group(1).trim + ' ' + matcher.group(2), ""))
        else list.append((matcher.group(1).trim, matcher.group(2)))
      }

    }
    list
  }

  def getStrBeforeWhere(source: String): String = {
    var tarStr = ""
    var flag = 0
    for (s <- source) {
      if (s != '%') tarStr += s

      if (s == '(') flag += 1
      else if (s == ')') flag -= 1
      else if (s == '%' && flag == 0) {
        return tarStr
      }
    }
    ""
  }

  def getSourceList(sources: String): ListBuffer[String] = {
    val source = sources.replace("/*", "#").replace("*/", "$")
    val list = ListBuffer[String]()
    var str = ""
    var comment = false
    var flag = 0
    for (c <- sources) {
      if (c == '#') comment = true
      if (c == '$') comment = false
      if (c == '(' && !comment) flag += 1
      else if (c == ')' && !comment) flag -= 1
      else if (c == ',' && flag == 0 && !comment) {
        list.append(str)
        str = ""
      }
      if (c != ',' || flag != 0) {
        if (c == '#') str += "/*"
        else if (c == '$') str += "*/"
        else str += c
      }
    }
    if (str != "") list.append(str)
    list
  }

  def isValid(str: String): Boolean = {
    str != null && str != ""
  }

  def isValidRule(rule: String): Boolean = {

    (isValid(rule) &&
      (
        !rule.contains("2.0无此表") &&
          !rule.contains("未迁移") &&
          !rule.contains("未知结构")
        )
      )
  }
}
